/*
 * 创建日期 2008-5-27
 *
 * 成都天和软件公司
 * 电话：028-85425861
 * 传真：028-85425861-8008
 * 邮编：610041 
 * 版权所有
 */
package  org.fantasy.common.db.util;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import  org.fantasy.common.util.DateUtil;
import  org.fantasy.common.util.StringUtil;

/**
 *  SQL工具类
 * @author: 王文成
 * @version: 1.0
 * @since 2009-1-12
 */
public class SqlUtil {
    private static final Log log = LogFactory.getLog(SqlUtil.class);

    private static final String EMPTY_STRING = new String();

    private SqlUtil() {

    }

    /**
     * 构造SQL in
     * 
     * @param values
     * @return
     */
    public static String getSqlIn(List values) {
        if (values.isEmpty())
            return EMPTY_STRING;
        StringBuffer buffer = new StringBuffer(64);
        for (int i = 0; i < values.size(); i++) {
            Object o = values.get(i);
            buffer.append("'");
            buffer.append(o.toString());
            buffer.append("'");
            buffer.append(",");
        }
        String in = "(" + buffer.substring(0, buffer.length() - 1) + ")";
        // log.debug(in);
        return in;
    }


    /**
     * 取得运行时的SQL
     * 
     * @param sql
     * @param paramMap
     * @return
     */
    public static final String getRuntimeSql(String sql, Map<String, Object> paramMap) {
        List<String> paramList = getParameterList(sql);
        for (String param : paramList) {
            Object value = paramMap.get(param);
            String regexp = "\\:" + param;
            if (value instanceof Number) {
                sql = sql.replaceAll(regexp, String.valueOf(value));
            } else if (value instanceof String) {
                sql = sql.replaceAll(regexp, "'" + String.valueOf(value) + "'");
            } else if (value instanceof java.util.Date) {
                String dateString = DateUtil.format((java.util.Date) value, "yyyy-MM-dd HH:mm:ss");
                sql = sql.replaceAll(regexp, "to_date('" + dateString + "','YYYY-MM-DD HH24:MI:SS')");
            } else {
                sql = sql.replaceAll(regexp, "'" + String.valueOf(value) + "'");
            }
        }
        return sql;
    }

    /**
     * 取得所有参数
     * 
     * @param sql
     * @param map
     * @param list
     * @return
     */
    public static List<String> getParameterList(String sql) {
        String regex = "\\:(\\w+)";
        Pattern pattern = Pattern.compile(regex);
        Matcher matcher = pattern.matcher(sql);
        List<String> params = new ArrayList<String>();
        while (matcher.find())
            params.add(matcher.group(1));
        return params;
    }

    /**
     * 构造SQL or
     * 
     * @param values
     * @return
     */
    public static String getSqlOr(List values, String colName) {
        if (values.isEmpty())
            return EMPTY_STRING;
        StringBuffer sql = new StringBuffer(64);
        sql.append("(");
        for (int i = 0; i < values.size(); i++) {
            Object value = values.get(i);
            if (i == 0)
                sql.append(" " + colName + "='" + value + "'");
            else
                sql.append(" OR " + colName + "='" + value + "'");
        }
        sql.append(")");
        return sql.toString();
    }

    /**
     * 构造SQL or
     * 
     * @param values
     * @return
     */
    public static String getSqlOr(String value, String colName) {
        if (!StringUtil.isValid(value))
            return "(1=1)";
        List values = Arrays.asList(value.split(","));
        return getSqlOr(values, colName);
    }

    public static void main(String[] args) {

    }
}
